package cn.melon.mod.sc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import cn.melon.Helper.DBHelper;
import cn.melon.Helper.LoggerHelper;
import cn.melon.base.IBaseExtractor;
import net.ucanaccess.jdbc.UcanaccessSQLException;

public class IStDaolmpl implements IStDao {

    public String TAG = LoggerHelper.makeLogTag(IStDao.class);
    public Logger stDaoImplLogger = LoggerHelper.serverLogger();

    private DBHelper<StInfo> dbHelper = new DBHelper<StInfo>();

    private String sqlCreate = "CREATE TABLE course_select (" 
                            + "student_num TEXT(10)," 
                            + "class_table TEXT(10240)"
                            + ")";

    private IBaseExtractor<StInfo> extractor = new IBaseExtractor<StInfo>() {
        @Override
        public List<StInfo> convertToList(ResultSet rs) {

            if (rs == null) {
                return null;
            }

            List<StInfo> infoList = new ArrayList<StInfo>();
            try {
                while (rs.next()) {
                    StInfo list = new StInfo();

                    list.setStudentNumber(rs.getString("student_num"));
                    list.setClassTable(rs.getString("class_table"));

                    infoList.add(list);
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
            return infoList;
        }
    };

    private StInfo getInfo(String studentNumber) {
        String sql = null;
        sql = "SELECT * FROM  course_select where student_num= ?";
        String[] paras = new String[] { studentNumber };
        List<StInfo> classList = null;
        StInfo classInfo = null;
        try {
            classList = dbHelper.sqlQuery(sql, paras, extractor);
        } catch (UcanaccessSQLException e) {
            e.printStackTrace();
        }

        if (classList != null && classList.size() > 0) {
            classInfo = classList.get(0);
            stDaoImplLogger.info(TAG + classInfo.getStudentNumber());
        }

        return classInfo;
    }

    public void onCreate() {
        try {
            dbHelper.sqlExecute(sqlCreate, new String[] {});
        } catch (UcanaccessSQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public StInfo searchOneStudent(String studentNumber) {
        
        return getInfo(studentNumber);
    }

    @Override
    public boolean deleteOneStudent(String studentNumber) {
        String sqlDelete = "DELETE FROM course_select WHERE student_num = ?";
        return dbHelper.sqlUpdate(sqlDelete, new String[]{studentNumber});
    }

    @Override
    public List<StInfo> showAllStudent() {
        
        String sqlQuery = "SELECT * FROM course_select";
        try {
            List<StInfo> infoList = dbHelper.sqlQuery(sqlQuery, new String[]{}, extractor);
            return infoList;
        } catch (UcanaccessSQLException e) {
            e.printStackTrace();
        }

        return null;
    }


    @Override
    public boolean addMessage(String classNumber, String studentNumber) {
        String sql = null;
        StInfo info = getInfo(studentNumber);
        String[] paras = new String[2];

        if (info == null) {
            stDaoImplLogger.debug(TAG + "INFO is NULL"); 
            sql = "INSERT INTO course_select" + "(student_num, class_table)" + "VALUES " + "(?, ?)";
            info = new StInfo();
            info.setStudentNumber(studentNumber);
            info.addClass(classNumber);
            paras[0] = info.getStudentNumber();
            paras[1] = info.tableToJson();
        } else {
            sql = "UPDATE course_select SET class_table=? WHERE student_num =?";
            info.addClass(classNumber);
            paras[0] = info.tableToJson();
            paras[1] = info.getStudentNumber();
        }

        return dbHelper.sqlUpdate(sql, paras);
    }

    @Override
    public boolean deleteMessage(String classNumber, String studentNumber) {
        
        String sql = null;
        StInfo list = getInfo(studentNumber);
        String[] paras = new String[2];
        if (list == null) {
            stDaoImplLogger.info(TAG + "不存在此学生的选课信息");
            return false;
        }

        list.remove(classNumber);

        sql = "UPDATE course_select SET class_table = ? WHERE student_num = ?";

        paras[0] = list.tableToJson();
        paras[1] = list.getStudentNumber();

        return dbHelper.sqlUpdate(sql, paras);
    }

    @Override
    public boolean addOneStudent(StInfo info) {
        String sql = "INSERT INTO course_select" 
                    + "(student_num, class_table)" 
                    + "VALUES " 
                    + "(?, ?)";
        
        String[] paras = new String[2];
        paras[0] = info.getStudentNumber();
        paras[1] = info.tableToJson();

        return dbHelper.sqlUpdate(sql, paras);
    }

    @Override
    public List<ScInfo> getAllInfo(String studentNumber) {
        
        StInfo stInfo = searchOneStudent(studentNumber);
        List<String> classNumList = stInfo.getClassTable();         // 先获取课程列表

        IScDao scDaolmpl = new IScDaolmpl();
        List<ScInfo> scInfoList = new ArrayList<>();

        for (String classNum : classNumList) {
            ScInfo info =  scDaolmpl.getInfo(classNum);             // 通过课程列表获取完整课程信息
            scInfoList.add(info);
        }
        return scInfoList;                                          // 返回详细的课程信息
    }
}
